/*
 *  Copyright 2010 Mahbub Mozadded Sowrov.
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *  under the License.
 */
package com.sowrov.afrigis.tools.sqlite;

import com.sowrov.afrigis.tools.app.timesheet_manager.TaskManagerApp;
import com.sowrov.afrigis.tools.timesheet.Activity;
import com.sowrov.afrigis.tools.timesheet.PostData;
import com.sowrov.afrigis.tools.timesheet.PostDataList;
import com.sowrov.afrigis.tools.timesheet.Project;
import com.sowrov.util.logsystem.ChronicleLogger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * <p>Instance of <code>DatabaseManager</code> is thread safe.</p>
 *
 * @author sowrov
 */
public class DatabaseManager {
    public static final String PROJECT_TABLE_NAME = "project";
    public static final String ACTIVITY_TABLE_NAME = "activity";
    public static final String USER_TABLE_NAME = "user";
    public static final String POST_DATA_TABLE_NAME = "postdata";
    public static final String MACRO_DATA_TABLE_NAME = "macrodata";
    private static final String MACRO_DATA_COUNT_TABLE_NAME = "macrodata_count";
    private static final String POST_DATA_COUNT_TABLE_NAME = "postdata_count";
    private static final String POST_DATA_TOTAL_TABLE_NAME = "postdata_total";

    private DbTable userTable = null;
    private DbTable projectTable = null;
    private DbTable activityTable = null;
    private DbTable postDataTable = null;
    private DbTable postDataCountTable = null;
    private DbTable postDataTotal = null;
    private DbTable macroDataTable = null;
    private DbTable macroDataCountTable = null;
    /**database connection holder*/
    private Connection dataConnection;
    /**sql statement holder*/
    private Statement statement;
    /**database file name*/
    private String databaseName = "timesheet.db";

    private int lastIncompleteMonth = -1;
    private int lastIncompleteYear = -1;
    private ArrayList<Integer>incompleteDays = new ArrayList<Integer>();
    
//    private String projectTable = PROJECT_TABLE_NAME+"(prid INTEGER PRIMARY KEY, name, rate INTEGER)";
//    private String activityTable = ACTIVITY_TABLE_NAME+"(acid INTEGER, name, prid INTEGER)";
//    private String userTable = USER_TABLE_NAME+"(id INTEGER PRIMARY KEY, name, password, url, port, interval)";
//    private String postDataTable = POST_DATA_TABLE_NAME+"(id INTEGER PRIMARY KEY, recordid, prid, acid, date, description,"
//            + " duration, billable, submited)";

    /**
     * default constructor, create a database manager object with null database connection and statement
     */
    public DatabaseManager() {
        this.dataConnection = null;
        this.statement = null;
    }

    /**
     * construct a database manager with given database name and null connection and statement
     * 
     * @param databaseName database file name
     */
    public DatabaseManager(String databaseName) {
        this.dataConnection = null;
        this.statement = null;
        this.databaseName = databaseName;
    }

    /**
     * prepare sqlite connection
     * 
     * @throws ClassNotFoundException if sqlite JDBC class not found
     * @throws SQLException database getConnection or createStatement method can return this exception
     */
    public final void prepareConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        this.dataConnection = DriverManager.getConnection("jdbc:sqlite:" + this.databaseName);
        this.statement = this.dataConnection.createStatement();
    }

    public void prepairTables() {
        ChronicleLogger.getInstance().logDebug("Prepairing Tables");
        this.userTable = new DbTable(DatabaseManager.USER_TABLE_NAME);
        //id, name, password, url, port, interval
        this.userTable.addPrimaryKeyColumn("id", "INTEGER");
        this.userTable.addColumn("name");
        this.userTable.addColumn("password");
        this.userTable.addColumn("url");
        this.userTable.addColumn("port");
        this.userTable.addIntegerColumn("interval");
        this.userTable.addColumn("last_sync_time");

        this.projectTable = new DbTable(DatabaseManager.PROJECT_TABLE_NAME);
        //prid INTEGER PRIMARY KEY, name, rate INTEGER
        this.projectTable.addPrimaryKeyColumn("prid", "INTEGER");
        this.projectTable.addColumn("name");
        this.projectTable.addIntegerColumn("rate");

        this.activityTable = new DbTable(DatabaseManager.ACTIVITY_TABLE_NAME);
//        acid INTEGER, name, prid INTEGER
        this.activityTable.addIntegerColumn("acid");
        this.activityTable.addColumn("name");
        this.activityTable.addIntegerColumn("prid");

        this.postDataTable = new DbTable(DatabaseManager.POST_DATA_TABLE_NAME);
//        id INTEGER PRIMARY KEY, server_id, prid, acid, date, description,"
//            + " duration, billable, status INTEGER
        this.postDataTable.addPrimaryKeyColumn("id", "INTEGER");
        this.postDataTable.addColumn("server_id");
        this.postDataTable.addColumn("prid");
        this.postDataTable.addColumn("acid");
        this.postDataTable.addColumn("date");
        this.postDataTable.addColumn("description");
        TableColumn durationColumn = new TableColumn("duration");
        durationColumn.addAttribute("REAL");
        this.postDataTable.addColumn(durationColumn);
        this.postDataTable.addColumn("billable");
        this.postDataTable.addIntegerColumn("status"); //submitted-2/modified - 1/new - 0

        this.postDataCountTable = new DbTable(DatabaseManager.POST_DATA_COUNT_TABLE_NAME);
        this.postDataCountTable.addIntegerColumn("id");
        this.postDataCountTable.addIntegerColumn("lastId");
        this.postDataCountTable.addIntegerColumn("totalRows");

        this.postDataTotal = new DbTable(DatabaseManager.POST_DATA_TOTAL_TABLE_NAME);
        this.postDataTotal.addPrimaryKeyColumn("date", "");
        this.postDataTotal.addColumn(durationColumn);//duration
//        this.postDataTotal.addIntegerColumn("checked"); //
        this.macroDataTable = new DbTable(DatabaseManager.MACRO_DATA_TABLE_NAME);
//        id INTEGER PRIMARY KEY, server_id, prid, acid, date, description,"
//            + " duration, billable, status INTEGER
        
        this.macroDataTable.addPrimaryKeyColumn("id", "INTEGER");
        this.macroDataTable.addIntegerColumn("projectIndex");
        this.macroDataTable.addColumn("prid");
        this.macroDataTable.addIntegerColumn("activityIndex");
        this.macroDataTable.addColumn("acid");
        this.macroDataTable.addColumn("description");
        this.macroDataTable.addColumn(durationColumn);
        this.macroDataTable.addColumn("billable");

        this.macroDataCountTable = new DbTable(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME);
        this.macroDataCountTable.addIntegerColumn("id");
        this.macroDataCountTable.addIntegerColumn("lastId");
        this.macroDataCountTable.addIntegerColumn("totalRows");
    }
    /**
     * create timesheet tables if they do not exist
     * tables are: project, activity, user, postdata
     * @throws SQLException
     */
    public final void createTables() throws SQLException {
        if(this.postDataTable == null) {
            this.prepairTables();
        }
        ChronicleLogger.getInstance().logDebug(this.projectTable.getTableCreateStatement());
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.projectTable.getTableCreateStatement());
        TaskManagerApp.splashScreen.setStatus("initializing db table", 25);
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.activityTable.getTableCreateStatement());
        TaskManagerApp.splashScreen.setStatus("initializing db table", 26);
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.userTable.getTableCreateStatement());
        TaskManagerApp.splashScreen.setStatus("initializing db table", 27);
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.postDataTable.getTableCreateStatement());
        TaskManagerApp.splashScreen.setStatus("initializing db table", 29);
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.postDataCountTable.getTableCreateStatement());
        if(this.getData(this.POST_DATA_COUNT_TABLE_NAME, this.postDataCountTable.getColumnNames(), "").isEmpty()) {
            ArrayList<String> tmp = new ArrayList<String>();
            tmp.add("1");
            tmp.add("0");
            tmp.add("0");
            this.insertIntoTable(this.POST_DATA_COUNT_TABLE_NAME, tmp);
        }
        TaskManagerApp.splashScreen.setStatus("initializing db table", 30);
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.postDataTotal.getTableCreateStatement());
        
        //macro table
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.macroDataTable.getTableCreateStatement());
        
        this.statement.execute("CREATE TABLE IF NOT EXISTS " + this.macroDataCountTable.getTableCreateStatement());
        if(this.getData(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, this.macroDataCountTable.getColumnNames(), "").isEmpty()) {
            ArrayList<String> tmp = new ArrayList<String>();
            tmp.add("1");
            tmp.add("0");
            tmp.add("0");
            this.insertIntoTable(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, tmp);
        }
        TaskManagerApp.splashScreen.setStatus("initializing db table", 32);
    }

    public DbTable getActivityTableInfo() {
        return activityTable;
    }

    public DbTable getPostDataTableInfo() {
        return postDataTable;
    }

    public DbTable getProjectTableInfo() {
        return projectTable;
    }

    public DbTable getUserTableInfo() {
        return userTable;
    }

    public final void dropTable(String tableName) throws SQLException {
        this.statement.execute("DROP TABLE IF EXISTS " + tableName);
    }

    public boolean insertIntoTable(String tableName, ArrayList<String> dataList) throws SQLException {
        if (dataList == null || dataList.isEmpty()) {
            return false;
        }

        if (this.dataConnection == null) {
            throw new SQLException("Ensure that prepareConnection method execute correctly");
        }

        StringBuilder queryBuild = new StringBuilder("INSERT INTO ");
        queryBuild.append(tableName);
        //queryBuild.append(" VALUES ('").append(dataList.get(0)).append("'");
        queryBuild.append(" VALUES (?");
        for (int i = 1; i < dataList.size(); i++) {
            queryBuild.append(", ?");
        }
        queryBuild.append(");");
//        System.out.println("query: " + queryBuild.toString());
        PreparedStatement prep = this.dataConnection.prepareStatement(queryBuild.toString());
        queryBuild.append("=");
        for (int i = 0; i < dataList.size(); i++) {
            prep.setString(i + 1, dataList.get(i));
            queryBuild.append(dataList.get(i)).append(" ");
//            System.out.println("data "+i+": "+dataList.get(i));
        }
        //prep.addBatch();
        ChronicleLogger.getInstance().logDebug(queryBuild.toString());
        prep.execute();
        return true;
    }

    public void updateTable (String tableName, ArrayList<String>column, ArrayList<String> value, String whereCl)
            throws SQLException {
        if (column == null || column.isEmpty() || value == null || value.isEmpty()) {
            return;
        }
        if (this.dataConnection == null) {
            throw new SQLException("Ensure that prepareConnection method execute correctly");
        }
        StringBuilder quBuilder = new StringBuilder("UPDATE ");
        quBuilder.append(tableName);
        quBuilder.append(" SET ");
        for (int i = 0; i < column.size(); i++) {
            if (i > 0) {
                quBuilder.append(", ");
            }
            quBuilder.append("\"").append(column.get(i)).append("\" = '").append(value.get(i)).append("'");
        }
        if (whereCl != null && !whereCl.equals("")) {
            quBuilder.append(" where ").append(whereCl);
        }
        ChronicleLogger.getInstance().logDebug(quBuilder.toString());
        this.statement.executeUpdate(quBuilder.toString());
    }

    public void deleteData(String tableName, String whereCl) throws SQLException {
        if (tableName.equals("") || whereCl.equals("")) {
            throw new SQLException("tablename or where clause can not be blank");
        }
        if (this.dataConnection == null) {
            throw new SQLException("Ensure that prepareConnection method execute correctly");
        }
        this.statement.executeUpdate("DELETE FROM "+tableName+" where "+whereCl);
    }
    /**
     * Retrieve columns from a given table. If column names are null or empty it will select all columns
     * @param tableName
     * @param columns
     * @param whereCl
     * @return
     * @throws SQLException
     */
    public ArrayList<ArrayList<String>> getData(String tableName, ArrayList<String> columns, String whereCl) throws
            SQLException {
        if (this.statement == null) {
            throw new SQLException("Ensure that prepareConnection method execute correctly");
        }
        ArrayList<ArrayList<String>> values = new ArrayList<ArrayList<String>>();
        StringBuilder columnBd = new StringBuilder();

        if (columns == null || columns.isEmpty()) {
            columnBd.append("*");
        } else {
            columnBd.append(columns.get(0));
            for (int i = 1; i < columns.size(); i++) {
                columnBd.append(",");
                columnBd.append(columns.get(i));
            }
        }
        whereCl = whereCl.trim();
        if (!whereCl.equals("")) {
            whereCl = "WHERE "+whereCl;
        }
        String sql = "SELECT " + columnBd.toString() + " FROM " + tableName + " "+ whereCl + ";";
        ChronicleLogger.getInstance().logDebug(sql);
        ResultSet rs = this.statement.executeQuery(sql);
        while (rs.next()) {
            ArrayList<String> arrayList = new ArrayList<String>();

            /*for (int i = 0; i < columns.size(); i++) {
            arrayList.add(rs.getString(i + 1));
            }*/
            int i = 1;
            String val;
            while (true) {
                try {
                    val = rs.getString(i++);
                } catch (SQLException ex) {
                    break;
                }
                arrayList.add(val);
            }
            values.add(arrayList);
            //System.out.println("name = " + rs.getString("name"));
            //System.out.println("job = " + rs.getString("occupation"));
        }
        rs.close();
        return values;
    }

    /**
     * Return the 1st row of the user table
     * 
     * @return
     */
    public ArrayList<String> getUser() {
        ArrayList<String> user = new ArrayList<String>();
        try {
            ArrayList<ArrayList<String>> responsedata = this.getData("user", this.userTable.getColumnNames(),
                    "id='1'");
            if (!responsedata.isEmpty()) {
                return responsedata.get(0);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    /**
     * Insert a post to postData table and return the insertion id of that post,
     * which can be use to delete or update the data.
     * 
     * @param serverId
     * @param projectId
     * @param activityId
     * @param date
     * @param description
     * @param duration floating point string value
     * @param billable true / false
     * @param status submitted-2, modified - 1, new - 0
     * @throws SQLException
     */
    public int insertPostToDataBase(String serverId, String projectId, String activityId, String date,
            String description, String duration, String billable, int status) throws SQLException{
//        id INTEGER PRIMARY KEY, server_id, prid, acid, date, description,"
//            + " duration, billable, status INTEGER
        ArrayList<ArrayList<String>> info = this.getData(DatabaseManager.POST_DATA_COUNT_TABLE_NAME, this.postDataCountTable.getColumnNames(), "");
        if(info.isEmpty()) {
            throw new SQLException("Info table is empty");
        }
        this.lastIncompleteMonth = -1;
        int lastId = Integer.parseInt(info.get(0).get(1));
        int newId = lastId+1;
        int totalRow = Integer.parseInt(info.get(0).get(2))+1;
        ArrayList<String> data = new ArrayList<String>();
        data.add(newId+""); //0
        data.add(serverId); //1
        data.add(projectId); //2
        data.add(activityId); //3
        data.add(date); //4
        data.add(description); //5
        data.add(duration); //6
        data.add(billable); //7
        data.add(status+""); //8
        if(!this.insertIntoTable(DatabaseManager.POST_DATA_TABLE_NAME, data)){
            throw new SQLException("Couldn't insert in "+DatabaseManager.POST_DATA_TABLE_NAME);
        }
        data.clear();
        data.add("1");
        data.add(newId+"");
        data.add(totalRow+"");
        this.updateTable(DatabaseManager.POST_DATA_COUNT_TABLE_NAME, this.postDataCountTable.getColumnNames(), data, "id='1'");
        return newId;
    }
    
    public int insertMacroData(
                int projectIndex, String projectId, int activityIndex, String activityId, String description, 
                String duration, String billable ) throws SQLException{
//        id INTEGER PRIMARY KEY, server_id, prid, acid, date, description,"
//            + " duration, billable, status INTEGER
        ArrayList<ArrayList<String>> info = this.getData(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, 
                this.macroDataCountTable.getColumnNames(), "");
        if(info.isEmpty()) {
            throw new SQLException("Macro table Info is empty");
        }
        this.lastIncompleteMonth = -1;
        int lastId = Integer.parseInt(info.get(0).get(1));
        int newId = lastId+1;
        int totalRow = Integer.parseInt(info.get(0).get(2))+1;
        ArrayList<String> data = new ArrayList<String>();
        data.add(newId+""); //0
        data.add(projectIndex+""); //1
        data.add(projectId); //2
        data.add(activityIndex+"");//3
        data.add(activityId); //4
        data.add(description); //5
        data.add(duration); //6
        data.add(billable); //7
        if(!this.insertIntoTable(DatabaseManager.MACRO_DATA_TABLE_NAME, data)){
            throw new SQLException("Couldn't insert in "+DatabaseManager.MACRO_DATA_TABLE_NAME);
        }
        data.clear();
        data.add("1");
        data.add(newId+"");
        data.add(totalRow+"");
        this.updateTable(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, this.macroDataCountTable.getColumnNames(), data, "id='1'");
        return newId;
    }

    public int saveMacroData(PostData pData) throws SQLException {
        return this.insertMacroData(pData.getProjectIndex(), pData.getProjectId(), pData.getActivityIndex(), 
                pData.getActivityId(), pData.getDescription(), pData.getDecimalTime(), pData.getBillable());
    }
    
    public void deleteMacroData(int id) throws SQLException {
        ArrayList<ArrayList<String>> info = this.getData(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, this.macroDataCountTable.getColumnNames(), "");
        if(info.isEmpty()) {
            throw new SQLException("Macro Info table is empty");
        }
        this.deleteData(DatabaseManager.MACRO_DATA_TABLE_NAME, "id="+id);
        ArrayList<String>data = new ArrayList<String>();
        int totalRow = Integer.parseInt(info.get(0).get(2))-1;
        data.add("1");
        data.add(info.get(0).get(1));
        data.add(totalRow+"");
        this.updateTable(DatabaseManager.MACRO_DATA_COUNT_TABLE_NAME, this.macroDataCountTable.getColumnNames(), data, "id='1'");
    }
    
    public void updateMacroData(PostData pData) throws SQLException {
        if (pData == null) {
            ChronicleLogger.getInstance().logDebug("MacroData is null");
        }
        ArrayList<String> data = new ArrayList<String>();
        data.add(pData.getLocalId()+""); //0
        data.add(pData.getProjectIndex()+"");//1
        data.add(pData.getProjectId()); //2
        data.add(pData.getActivityIndex()+"");//3
        data.add(pData.getActivityId()); //4
        data.add(pData.getDescription()); //5
        data.add(pData.getDecimalTime()); //6
        data.add(pData.getBillable()); //7
        this.updateTable(DatabaseManager.MACRO_DATA_TABLE_NAME, this.macroDataTable.getColumnNames(), data,
                "id='"+pData.getLocalId()+"'");
    }

    public PostDataList getMacroDataListe() throws SQLException {
        ChronicleLogger.getInstance().logDebug("Get macro dat");
        ArrayList<ArrayList<String>> data = this.getData(DatabaseManager.MACRO_DATA_TABLE_NAME,
                this.macroDataTable.getColumnNames(), "");
        
        return this.makeMacroDataList(data);
    }
    
    private PostDataList makeMacroDataList(ArrayList<ArrayList<String>> data) throws SQLException {
        PostDataList postDataList = new PostDataList();
        for (int i=0; i<data.size(); i++) {
            ArrayList<String> rowData = data.get(i);
            Project project = this.getProjectById(rowData.get(1), false);
            PostData pdata = new PostData(Integer.parseInt(rowData.get(0)), //macro id will be the local id
                        rowData.get(1), //project id 
                        project.getRate(), //project rate
                        rowData.get(2), //activity id
                        "", //no date for macro
                        rowData.get(3), //description
                        rowData.get(4), //time
                        rowData.get(5).equalsIgnoreCase("True")?true:false//billable
                    );
            postDataList.addPostData(pdata);
        }
        return postDataList;
    }
    
    public void updatePostData(PostData pData) throws SQLException {
        ArrayList<String> data = new ArrayList<String>();
        data.add(pData.getLocalId()+""); //0
        data.add(pData.getServerId()); //1
        data.add(pData.getProjectId()); //2
        data.add(pData.getActivityId()); //3
        data.add(pData.getStrDate()); //4
        data.add(pData.getDescription()); //5
        data.add(pData.getDecimalTime()); //6
        data.add(pData.getBillable()); //7
        data.add(pData.getStatus()+""); //8
        
        this.updateTable(DatabaseManager.POST_DATA_TABLE_NAME, this.postDataTable.getColumnNames(), data,
                "id='"+pData.getLocalId()+"'");
    }
    
    public void updatePostDataServerId(PostData pData) throws SQLException {
        ArrayList<String>column = new ArrayList<String>();
        column.add("server_id");
        column.add("status");
        ArrayList<String>value = new ArrayList<String>();
        value.add(pData.getServerId());
        value.add(pData.getStatus()+"");
        this.updateTable(DatabaseManager.POST_DATA_TABLE_NAME, column, value, "id='"+pData.getLocalId()+"'");
    }

    private PostDataList makePostDataList(ArrayList<ArrayList<String>> data) throws SQLException {
        PostDataList postDataList = new PostDataList();
        for (int i=0; i<data.size(); i++) {
            ArrayList<String> rowData = data.get(i);
            Project project = this.getProjectById(rowData.get(2), false);
            PostData pdata = new PostData(Integer.parseInt(rowData.get(0)), rowData.get(2), project.getRate(),
                    rowData.get(3),
                    rowData.get(4),
                    rowData.get(5),
                    rowData.get(6), rowData.get(7).equalsIgnoreCase("True")?true:false);
            pdata.setServerId(rowData.get(1));
            pdata.setStatus(Integer.parseInt(rowData.get(8)));
            postDataList.addPostData(pdata);
        }
        return postDataList;
    }

    public PostDataList getPostDataListByDate (String yearMonthDay) throws SQLException {
        ChronicleLogger.getInstance().logDebug("Get post data for "+yearMonthDay);
        ArrayList<ArrayList<String>> data = this.getData(DatabaseManager.POST_DATA_TABLE_NAME,
                this.postDataTable.getColumnNames(), "date='"+yearMonthDay+"' AND status <> '1'");
        
        return this.makePostDataList(data);
    }

    public PostDataList getPostDataListByLocalId(String id) throws SQLException {
        ArrayList<ArrayList<String>> data = this.getData(DatabaseManager.POST_DATA_TABLE_NAME,
                this.postDataTable.getColumnNames(), "id='"+id+"'");

        return this.makePostDataList(data);
    }

    public PostDataList getPostDataListByStatus(int[] status, int limit) throws SQLException {
        if (status==null || status.length==0 || limit==0) {
            ChronicleLogger.getInstance().logCritical("Status list may not be blank and limit may not be zero");
            return new PostDataList();
        }
        StringBuilder sb = new StringBuilder();
        for (int i=0; i<status.length; i++) {
            if(i>0) {
                sb.append(" OR ");
            }
            sb.append("status='").append(status[i]).append("'");
        }
        sb.append(" LIMIT ").append(limit);
        ArrayList<ArrayList<String>> data = this.getData(DatabaseManager.POST_DATA_TABLE_NAME,
                this.postDataTable.getColumnNames(), sb.toString());

        return this.makePostDataList(data);
    }

    public PostDataList getPostDataListByServerId (String serverId) throws SQLException {
        ArrayList<ArrayList<String>> data = this.getData(DatabaseManager.POST_DATA_TABLE_NAME,
                this.postDataTable.getColumnNames(), "server_id='"+serverId+"'");

        return this.makePostDataList(data);
    }
    public void deletePostData(String id) throws SQLException {
        ArrayList<ArrayList<String>> info = this.getData(DatabaseManager.POST_DATA_COUNT_TABLE_NAME, this.postDataCountTable.getColumnNames(), "");
        if(info.isEmpty()) {
            throw new SQLException("Info table is empty");
        }
        this.deleteData(DatabaseManager.POST_DATA_TABLE_NAME, "id="+id);
        ArrayList<String>data = new ArrayList<String>();
        int totalRow = Integer.parseInt(info.get(0).get(2))-1;
        data.add("1");
        data.add(info.get(0).get(1));
        data.add(totalRow+"");
        this.updateTable(DatabaseManager.POST_DATA_COUNT_TABLE_NAME, this.postDataCountTable.getColumnNames(), data, "id='1'");
    }

    public boolean saveProjectAndActivity(ArrayList<Project> projects) {
        if (projects == null || projects.isEmpty()) {
            return false;
        }
        ArrayList<Project> oldProjects = this.getProjectAndActivity();
        ArrayList<Project> newProjects = new ArrayList<Project>();
        for (int i=0; i<projects.size(); i++) {
            boolean found = false;
            for (int j=0; j<oldProjects.size(); j++) {
                if (projects.get(i).getId().equalsIgnoreCase(oldProjects.get(j).getId())) {
                    found = true;
                    oldProjects.remove(j);
                    break;
                }
            }
            if (!found) {
                newProjects.add(projects.get(i));
            }
        }
        ChronicleLogger.getInstance().logDebug("Sent projects: "+projects.size()+" newprojects: "+newProjects.size());
        ArrayList<String>dataList = new ArrayList<String>();
        try {
            for (int i=0; i<newProjects.size(); i++) {
                dataList.clear();
                dataList.add(newProjects.get(i).getId());
                dataList.add(newProjects.get(i).getName());
                dataList.add(newProjects.get(i).getRate());
                this.insertIntoTable(DatabaseManager.PROJECT_TABLE_NAME, dataList);
                for (int j=0; j<newProjects.get(i).getActivityCount(); j++) {
                    dataList.clear();
                    dataList.add(newProjects.get(i).getActivityAt(j).getId());
                    dataList.add(newProjects.get(i).getActivityAt(j).getName());
                    dataList.add(newProjects.get(i).getId());
                    this.insertIntoTable(DatabaseManager.ACTIVITY_TABLE_NAME, dataList);
                }
            }
        } catch (SQLException ex) {
            ChronicleLogger.getInstance().logDebug("Insertion error: "+ex.getMessage());
            return false;
        }
        
        return true;
    }

    public Project getProjectById(String id, boolean includeActivity) throws SQLException {
        ArrayList<ArrayList<String>> responsedata = this.getData(DatabaseManager.PROJECT_TABLE_NAME,
                this.projectTable.getColumnNames(), "prid='"+id+"'");
        if (responsedata.isEmpty()) {
            return new Project();
        }
        Project proj = new Project();
        proj.setId(responsedata.get(0).get(0));
        proj.setName(responsedata.get(0).get(1));
        proj.setRate(responsedata.get(0).get(2));

        if (includeActivity) {
            ArrayList<ArrayList<String>> responseAct = this.getData("activity",
            this.activityTable.getColumnNames(), "prid='" + proj.getId() + "'");
            for (int j = 0; j < responseAct.size(); j++) {
                Activity act = new Activity();
                act.setId(responseAct.get(j).get(0));
                act.setName(responseAct.get(j).get(1));
                if (proj.getRate().equals("0") || proj.getRate().equals("")) {
                    act.setBillAble(false);
                } else {
                    act.setBillAble(true);
                }
                proj.addActivity(act);
            }
        }

        return proj;
    }
    /**
     * Retrieve Project and activity info from database
     * 
     * project(prid INTEGER PRIMARY KEY, name, rate INTEGER)
     * activity(acid INTEGER PRIMARY KEY, name, prid)
     * @return array list of project
     */
    public ArrayList<Project> getProjectAndActivity() {
        ArrayList<Project> project = new ArrayList<Project>();
        
        try {
            ArrayList<ArrayList<String>> responsedata = this.getData("project", this.projectTable.getColumnNames(),
                    "");
            for (int i = 0; i < responsedata.size(); i++) {
                Project proj = new Project();
                proj.setId(responsedata.get(i).get(0));
                proj.setName(responsedata.get(i).get(1));
                proj.setRate(responsedata.get(i).get(2));
                ArrayList<ArrayList<String>> responseAct = this.getData("activity",
                        this.activityTable.getColumnNames(), "prid='" + proj.getId() + "'");
                for (int j = 0; j < responseAct.size(); j++) {
                    Activity act = new Activity();
                    act.setId(responseAct.get(j).get(0));
                    act.setName(responseAct.get(j).get(1));
                    if (proj.getRate().equals("0") || proj.getRate().equals("")) {
                        act.setBillAble(false);
                    } else {
                        act.setBillAble(true);
                    }
                    proj.addActivity(act);
                }
                project.add(proj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return project;
    }

    public void saveDayTotalHour(String date, double totalHour) throws SQLException{
        ArrayList<ArrayList<String>> data =this.getData(DatabaseManager.POST_DATA_TOTAL_TABLE_NAME,
                this.postDataTotal.getColumnNames(), "date='"+date+"'");
        this.lastIncompleteMonth = -1;
        if (data.isEmpty()) {
            ArrayList<String> list = new ArrayList<String>();
            list.add(date);
            list.add(totalHour+"");
            this.insertIntoTable(DatabaseManager.POST_DATA_TOTAL_TABLE_NAME, list);
        } else {
            ArrayList<String>column = new ArrayList<String>();
            column.add("duration");
            ArrayList<String>value = new ArrayList<String>();
            value.add(totalHour+"");
            this.updateTable(DatabaseManager.POST_DATA_TOTAL_TABLE_NAME, column, value, "date='"+date+"'");
        }
    }

    public ArrayList<ArrayList<String>> getMonthTotalHour(int month, int year) throws SQLException {
        ArrayList<ArrayList<String>> data =this.getData(DatabaseManager.POST_DATA_TOTAL_TABLE_NAME,
                this.postDataTotal.getColumnNames(), "date LIKE '%/"+(month<10?"0":"")+month+"/"+year+"'");

        return data;
    }

    /**
     * return the list of all day number of the given month that don't have 8.5 hr registered
     * (don't include Friday and Saturday)
     * 
     * @param month
     * @param year
     * @return
     * @throws SQLException
     */

    public ArrayList<Integer> getIncompleteDaysOfMonth(int month, int year) throws SQLException {
        if (month == this.lastIncompleteMonth && year==this.lastIncompleteYear && !this.incompleteDays.isEmpty()) {
            ChronicleLogger.getInstance().logDebug("Returning Cached day list for month: "+month+" year: "+year);
            return this.incompleteDays;
        }
        this.lastIncompleteMonth = month;
        this.lastIncompleteYear = year;
        Calendar cl = Calendar.getInstance();
        cl.set(year, month, 1);
        int dayNum = cl.get(Calendar.DAY_OF_WEEK);
        int totalDays = cl.getActualMaximum(Calendar.DAY_OF_MONTH);
        boolean[] flags = new boolean[totalDays+1];
        for (int i=1; i<=totalDays; i++) {
            if(dayNum != Calendar.FRIDAY && dayNum != Calendar.SATURDAY) {
                flags[i] = false;
            } else {
                flags[i] = true;
            }
            dayNum++;
            dayNum%=8;
            dayNum = dayNum==0? 1 : dayNum;
        }
        
        ArrayList<ArrayList<String>> setDates = this.getMonthTotalHour(month+1, year);
        for (int i=0; i<setDates.size(); i++) {
            int xday = Integer.parseInt(setDates.get(i).get(0).substring(0, setDates.get(i).get(0).indexOf('/')));
            double hour = Double.parseDouble(setDates.get(i).get(1));
            if(xday<=totalDays && !(hour<8.5)) {
                flags[xday] = true;
            }
        }

        incompleteDays.clear();
        for(int i=1; i<=totalDays; i++) {
            if(!flags[i]) {
                incompleteDays.add(i);
            }
        }
        return incompleteDays;
    }

    /**
     * Check whether a given table name exist
     * @param tableName name of the table
     * @return true if a table with that name exist or false
     * @throws SQLException throw exception if sql statement object is null or executeQuery method throw it
     */
    public final boolean isTableExist(final String tableName) throws SQLException {
        if (this.statement == null) {
            throw new SQLException("Ensure that prepareConnection method execute correctly");
        }
        ResultSet rs = this.statement.executeQuery("SELECT name FROM sqlite_master WHERE name='" + tableName + "'");
        if (rs.next()) {
            rs.close();
            return true;
        } else {
            rs.close();
            return false;
        }
    }

    public static void main(String[] arg) throws SQLException, ClassNotFoundException {
        DatabaseManager databaseManager = new DatabaseManager("blankdb.db");
        databaseManager.prepareConnection();
        databaseManager.createTables();
        ArrayList<String> datalist = new ArrayList<String>();
        datalist.add("1");
        datalist.add("BlaBla");
        datalist.add("mypass");
        datalist.add("196.35.231.10");
        datalist.add("80");
        datalist.add("5");
        databaseManager.insertIntoTable("user", datalist);
        ArrayList<String> user = databaseManager.getUser();
        if (user.isEmpty()) {
            System.out.println("Ask for user name and password");
        } else {
            for (int i = 0; i < user.size(); i++) {
                System.out.println(user.get(i) + " ");
            }
        }

        /*System.out.println(databaseManager.isTableExist("user"));
        System.out.println(databaseManager.isTableExist("project"));
        System.out.println(databaseManager.isTableExist("bala"));
        System.out.println(databaseManager.isTableExist("activity"));
        System.out.println(databaseManager.isTableExist("mytable"));*/
    }

    public void updateLastSyncTime(long timeInMillis) throws SQLException {
        ArrayList<String> column = new ArrayList<String>();
        ArrayList<String> value = new ArrayList<String>();
        column.add("last_sync_time");
        value.add(timeInMillis+"");
        this.updateTable(DatabaseManager.USER_TABLE_NAME, column, value, "id='1'");
    }
}
